package com.edianping.store;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.edianping.bean.ProcessItemBean;
import com.edianping.bean.ProductItemBean;
import com.edianping.bean.ReviewItemBean;
import com.edianping.search.ReviewSearchManager;
import com.edianping.sphinx.SphinxException;
import com.edianping.util.LogCode;

public class ProductDBStore extends AbstractDBStore {
	protected static Logger log = Logger.getLogger(ProductDBStore.class);
	
	public static ProcessItemBean GetProductInfoById(int productId) {
		Connection conn = null;
		Statement stmt = null;
		String sql = "select * from product_info where id= " + productId;
		ProcessItemBean b = null;

		try {
			conn = pool.getConnection();
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(sql);

			ProductItemBean sbean;
			while (rs.next()) {
				String id = rs.getString("id");
				b = new ProcessItemBean(id);
				sbean = new ProductItemBean(id);
				sbean.setScore(rs.getInt("Score"));
				sbean.setShopid(rs.getString("id"));
				sbean.setReviews(GetReviewsByProductId( 
						Integer.parseInt(sbean.getId()))
					);
				b.setBean(sbean);
			}

			rs.close();
			stmt.close();

		} catch (Exception e) {
			log.error("[[" + LogCode.E_DB_SQL_ERROR + "]], SQL:[" + sql + "]", e);
		} finally {
			if (conn != null)
				pool.checkin(conn);
		}

		return b;
	}

	public static List<ProcessItemBean> GetProductInfos(int startindex, int count) {
		List<ProcessItemBean> result = new ArrayList<ProcessItemBean>();
		Connection conn = null;
		Statement stmt = null;
		String sql = "select * from product_info limit " + startindex + ", "
				+ count;

		try {
			conn = pool.getConnection();
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(sql);

			ProcessItemBean b;
			ProductItemBean sbean;
			while (rs.next()) {
				String id = rs.getString("id");
				b = new ProcessItemBean(id);
				sbean = new ProductItemBean(id);
				sbean.setScore(rs.getInt("Score"));
				sbean.setShopid(rs.getString("id"));
				sbean.setReviews( 
						GetReviewsByProductId( 
								Integer.parseInt(sbean.getId() ) 
							)
						);
				b.setBean(sbean);
				result.add(b);
			}

			rs.close();
			stmt.close();

		} catch (Exception e) {
			log.error("[[" + LogCode.E_DB_SQL_ERROR + "]], SQL:[" + sql + "]");
			log.debug(e);
			if (log.isDebugEnabled())
				log.debug(e);
		} finally {
			if (conn != null)
				pool.checkin(conn);
		}

		return result;
	}

	public static List<ReviewItemBean> GetReviewsByProductId(int productid) {
		List<ReviewItemBean> result = new ArrayList<ReviewItemBean>();

		Connection conn = null;
		Statement stmt = null;
		
		ReviewSearchManager mgr = new ReviewSearchManager( "localhost", 9312 );
		List<Integer> res =  new ArrayList<Integer>();
		try {
			res = mgr.SearchReviewByProductId( "", productid );
		} catch (SphinxException e1) {
			log.error( "Search failed" );
		}
		
		if( res.size() > 0 ){
			String idlist = "";
			int index = 1;
			for( int id : res ){
				if( index == res.size() ){
					idlist += id;
				}
				else{
					idlist += id +",";
				}
				index++;
			}
			log.info( "idlist is:"+idlist );
			
			String sql = "select *, ( select contribution from user_statistic where user_id=r.user_id) as reputation "
					+ "from review r where id in ( "+idlist+" )"
					+ " order by review_date desc";
	
			try {
				conn = pool.getConnection();
				stmt = conn.createStatement();
	
				ResultSet rs = stmt.executeQuery(sql);
	
				ReviewItemBean b;
				int count = 3;
				while (rs.next()) {
					b = new ReviewItemBean(rs.getString("id"));
					b.setWeight(count);
					b.setShopid(rs.getString("shop_id"));
					b.setRate(rs.getInt("rate"));
					b.setReality(rs.getInt("reality"));
					b.setService(rs.getInt("service"));
					b.setVelocity(rs.getInt("velocity"));
					b.setScore(rs.getInt("score"));
					b.setEgg_count(rs.getInt("egg_count"));
					b.setFlower_count(rs.getInt("flower_count"));
					b.setUserReputation(rs.getInt("reputation"));
					result.add(b);
	
					count++;
				}
	
				rs.close();
				stmt.close();
	
			} catch (Exception e) {
				log.error("[[" + LogCode.E_DB_SQL_ERROR + "]], SQL:[" + sql + "]");
				log.debug(e);
				if (log.isDebugEnabled())
					log.debug(e);
			} finally {
				if (conn != null)
					pool.checkin(conn);
			}
		}

		return result;
	}
	
	public static boolean UpdateProductInfo(ProcessItemBean item) {
		String sql = "update product_info set " + "score="
				+ ((ProductItemBean) item.getBean()).getScore() + ", rate="
				+ ((ProductItemBean) item.getBean()).getRate() + ", service="
				+ ((ProductItemBean) item.getBean()).getService() + ", reality="
				+ ((ProductItemBean) item.getBean()).getReality() + ", velocity="
				+ ((ProductItemBean) item.getBean()).getVelocity() + " where id="
				+ item.getId();

		return ExecuteSQL(sql);
	}
}
